package be.irail.examples;

import api.datastructs.Station;
import api.datastructs.Vehicle;
import be.irail.api.IRail;
import be.irail.api.data.Connection;
import be.irail.api.data.Stop;
import be.irail.api.data.TripNode;
import be.irail.api.data.VehicleInformation;

import java.io.IOException;
import java.net.SocketException;
import java.net.URLEncoder;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.Calendar;
import java.util.List;

public class IRailExample
{
    public static final String IRAIL="http://api.irail.be";

    public static void main(String[] argv) throws Exception, SocketException
    {
        IRail       iRail       =new IRail(IRAIL,"nl");
        Calendar    cal         =Calendar.getInstance();
          
        Class.forName("org.sqlite.JDBC");
        java.sql.Connection conn = DriverManager.getConnection("jdbc:sqlite:C:\\Users\\Marten\\Documents\\School\\1 master\\Infovis\\db\\Infovis_db.db");
   
       // loadStations( conn, iRail );
        
        loadTrains( conn, iRail );
        
    }
    
    public static void loadStations( java.sql.Connection conn, IRail iRail ) throws Exception
    {
    	Statement statement = null; 
    	statement = conn.createStatement();
        List<be.irail.api.data.Station> stations = iRail.getStations();
        String stationname = "";
        String stationId = "";
        double locX = 0;
        double locY = 0;
        
        for(be.irail.api.data.Station station : stations)
        {
        	stationId = station.getId();
        	stationname = station.getName();
        	locX = station.getLocation().getLatitude();
        	locY = station.getLocation().getLongitude();
        	PreparedStatement prep = conn.prepareStatement("insert into Stations values (?, ?,?,?);");        	
        	
        	prep.setString(1, stationId);
        	prep.setDouble(2, locX);
        	prep.setDouble(3, locY);
        	prep.setString(4, stationname);

        	conn.setAutoCommit(false);
        	prep.execute();
        	conn.setAutoCommit(true);
        }
    }
    

	public static void loadTrains( java.sql.Connection conn, IRail iRail ) throws Exception
    {
    	String trainId = "";
    	String dep, arr;
    	String depTime, arrTime;
    	be.irail.api.data.Vehicle vehicle = null;
    	List<Connection> connections = null;
    	
    	int distance;
    	
    	List<be.irail.api.data.Station> stations = iRail.getStations();
    	
    	for(be.irail.api.data.Station station1 : stations)
    	{
			if(!station1.getName().equals("Hasselt"))
			{
			try {
				 connections = iRail.getConnections( "Hasselt", URLEncoder.encode( station1.getName()) );
			} catch (IOException e) {
			    System.err.println("Caught IOException: "
			                        + e.getMessage());
			}
			 catch (org.xml.sax.SAXParseException e) {
			    System.err.println("SAXParseException: "
			                        + e.getMessage());
			 }
				
	            if(!connections.isEmpty())
    			{
    				for(Connection connection : connections)
    				{
    					     					 
    					trainId = connection.getDeparture().getVehicle().getId();
    					dep = connection.getDeparture().getStation().getName();
    					depTime = connection.getDeparture().getTime().toString();
    					arrTime = connection.getArrival().getTime().toString();
    					arr = connection.getArrival().getStation().getName();
    					distance = connection.getDuration();
    					
    					vehicle = connection.getDeparture().getVehicle();
    					VehicleInformation info = iRail.getVehicleInformation(vehicle);
    					
    					List<Stop> stops = info.getStops();
    					
    					for(Stop m_Stop : stops)
    					{
    						
    					}
    				
    					
    					PreparedStatement prep = conn.prepareStatement("SELECT * FROM Trains WHERE Train_id = ?");
    					prep.setString(1, trainId );
    					ResultSet rs = prep.executeQuery();
    					
    					if(!rs.next())
    					{    					      					
	    				    prep = conn.prepareStatement("insert into Trains values (?);");
	    					prep.setString(1, trainId);
	    					
	    					conn.setAutoCommit(false);
	    		        	prep.execute();
	    		        			    		        	
	    		        	conn.setAutoCommit(true);	
	    		        	
	    		        	//insert into connections
	    		        	prep = conn.prepareStatement("insert into Connections values (?, ?, ?, ?, ?, ?);");
	    		        	prep.setString(1, trainId);
	    		        	prep.setString(2, dep);
	    		        	prep.setString(3, arr);
	    		        	prep.setString(4, depTime.toString());
	    		        	prep.setString(5, arrTime.toString());
	    		        	prep.setInt(6, distance);
	    		        	conn.setAutoCommit(false);
	    		        	prep.execute();
	    		        			    		        	
	    		        	conn.setAutoCommit(true);	
	    		        	}
    					rs.close();
    				}
    			}
        	}    
    	}
    }
}


